#!/bin/bash
# upload_sftp.sh
# sftp -oPort=9999 qingniu_telesphorus@sftp-p2.zhonganzaixian.cn:/qingniu/telesphorus/upfile  aPlT2Jkn

echo "`date "+%F %T"` START #############################################################################"

. ~/.bash_profile

# 定义变量
DATE1=`date -d yesterday +%F`
DATE2=`date +%F`
FOLDER=`date -d yesterday +%Y%m%d`

# 导出明细报表
sqlplus -s / as sysdba <<EOF
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
spool ../data/R_DETAIL_`date -d yesterday +%Y%m%d`.csv
select 'SESSION_ID' || ',' || 'REGION_ID' || ',' || 'CALLTYPE' || ',' ||
       'AGENT_ID' || ',' || 'AGENT_NAME' || ',' || 'AGENT_DN' || ',' ||
       'START_TIME' || ',' || 'END_TIME' || ',' || 'LOCAL_URL' || ',' ||
       'REMOTE_URL' || ',' || 'SKILL_ID' || ',' || 'SKILL_DESC' || ',' ||
       'IVR_DURATION' || ',' || 'QUEUE_DURATION' || ',' || 'ALERT_DURATION' || ',' ||
       'TALK_DURATION' || ',' || 'ACW_DURATION' || ',' || 'END_TYPE'
  from dual
union all
select to_char(session_id,'xxxxxxxxxxxxxxxx') || ',' || REGION_ID || ',' || CALLTYPE || ',' || AGENT_ID || ',' ||
       AGENT_NAME || ',' || AGENT_DN || ',' || START_TIME || ',' ||
       END_TIME || ',' || LOCAL_URL || ',' || REMOTE_URL || ',' || SKILL_ID || ',' ||
       SKILL_DESC || ',' || IVR_DURATION || ',' || QUEUE_DURATION || ',' ||
       ALERT_DURATION || ',' || TALK_DURATION || ',' || ACW_DURATION || ',' ||
       END_TYPE
  from "0000063425".R_DETAIL
 where start_time > to_date('$DATE1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and start_time < to_date('$DATE2 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
spool off
exit
EOF
sleep 3

# 生成加密的明细报表
/usr/bin/expect <<EOF
spawn /usr/bin/gpg --recipient 8E029E40 --output ../data/R_DETAIL_encrypt_$FOLDER.csv --encrypt ../data/R_DETAIL_$FOLDER.csv
sleep 10
expect "(y/N)" { send "y\r" }
sleep 1
interact
EOF

# 上传到sftp上
/usr/bin/expect <<EOF
spawn sftp -oPort=9999 qingniu_telesphorus@sftp-p2.zhonganzaixian.cn:/qingniu/telesphorus/upfile
sleep 3
expect "password:" { send "aPlT2Jkn\r" }
sleep 2
expect "sftp>" { send "cd $FOLDER\r" }
sleep 1
expect "sftp>" { send "put ../data/R_DETAIL_encrypt_$FOLDER.csv\r" }
sleep 1
expect "sftp>" { send "exit\r" }
interact
EOF

echo "`date "+%F %T"` END ###############################################################################"
echo ""
echo ""